package com.xoa.service.party;


import com.xoa.dao.party.GridInfoMapper;
import com.xoa.dao.party.IllegalrentgroupMapper;
import com.xoa.dao.party.MapOwnerinfoMapper;
import com.xoa.model.party.GridInfo;
import com.xoa.model.party.Illegalrentgroup;
import com.xoa.util.ExcelUtil;
import com.xoa.util.ToJson;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.wrapper.BaseWrapper;
import com.xoa.util.page.PageParams;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by 程叶同 on 2018/1/9.
 * 出租屋
 */
@Service
public class IllegalRentService {
    @Autowired
    IllegalrentgroupMapper illegalrentgroupMapper;
    @Autowired
    MapOwnerinfoMapper mapOwnerinfoMapper;
    @Autowired
    GridInfoMapper gridInfoMapper;

    public BaseWrapper addRent(Illegalrentgroup illegalrentgroup){
        BaseWrapper baseWrapper=new BaseWrapper();
        if(illegalrentgroup!=null){
            int insert = illegalrentgroupMapper.insert(illegalrentgroup);
            if(insert>0){
                baseWrapper.setMsg("添加成功");
                baseWrapper.setFlag(true);
                baseWrapper.setStatus(true);
            }
        }
        return baseWrapper;
    }


public ToJson ecoudInsert(MultipartFile file, HttpServletRequest request){
          int count=0;//計數器
          ToJson json=new ToJson();
          try{
              SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
              if (file.isEmpty()) {
                  json.setMsg("请上传文件！");
                  json.setFlag(1);
                  return json;
              }else {
                  String fileName = file.getOriginalFilename();
                  if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                      String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                      int pos = fileName.indexOf(".");//判读当前系统
                      //读取配置文件
                      ResourceBundle rb = ResourceBundle.getBundle("upload");
                      String osName = System.getProperty("os.name");
                      StringBuffer path = new StringBuffer();
                      if (osName.toLowerCase().startsWith("win")) {
                          path = path.append(rb.getString("upload.win"));
                      } else {
                          path = path.append(rb.getString("upload.linux"));
                      }
                      String extName = fileName.substring(pos);
                      String newFileName = uuid + extName;
                      File dest = new File(path.toString(), newFileName);
//                    file.transferTo(dest);
//                    // 将文件上传成功后进行读取文件
//                    // 进行读取的路径
//                    String readPath = path.append(newFileName).toString();
//                    InputStream in = new FileInputStream(readPath);
                      InputStream in =  file.getInputStream();

                      HSSFWorkbook excelObj = new HSSFWorkbook(in);
                      HSSFSheet sheetObj = excelObj.getSheetAt(0);
                      Row row = sheetObj.getRow(0);
                      int colNum = row.getPhysicalNumberOfCells();
                      int lastRowNum = sheetObj.getLastRowNum();
                      Illegalrentgroup illegalrentgroup = null;
                      String subAra="";
                      String buildingDoor="";
                      for (int i = 1; i <= lastRowNum; i++) {
                          illegalrentgroup = new Illegalrentgroup();
                          row = sheetObj.getRow(i);
                          String value = "";
                          if (row != null) {
                              for (int j = 0; j < colNum; j++) {
                                  Cell cell = row.getCell(j);
                                  if (cell != null) {
                                      switch (j) {
                                          case 0:
                                              //所属街道
                                              illegalrentgroup.setSubStreet(cell.getStringCellValue());
                                              break;
                                          case 1:
                                              //所属区社区ID
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setSubArea("");
                                              GridInfo gridInfo=gridInfoMapper.selectByUGridName(cell.getStringCellValue());
                                              if(gridInfo!=null){
                                                  illegalrentgroup.setSubArea(gridInfo.getGridId().toString());
                                              }else {
                                                  illegalrentgroup.setSubArea("");
                                              }
                                              break;
                                          case 2:
                                              //楼房门号
                                              buildingDoor=cell.getStringCellValue();
                                              illegalrentgroup.setBuildingDoor(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 3:
                                              //存在問題
                                              illegalrentgroup.setExistignProblem(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 4:
                                              //房間數目
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  illegalrentgroup.setRoom(cell.getNumericCellValue()+"");
                                              }else {
                                                  illegalrentgroup.setRoom(cell.getStringCellValue()==null?"":cell.getStringCellValue()+"");
                                              }
                                              break;
                                          case 5:
                                              //隔斷閒數
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setPartitionedRoom(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 6:
                                              //建筑面积
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setConstructionArea(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 7:
                                              //居住人数
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                             illegalrentgroup.setPersonNumber(cell.getStringCellValue()==null?"":cell.getStringCellValue()+"");
                                              break;
                                          case 8:
                                              //房屋來源
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setHouseSource(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 9:
                                              //房屋性質
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setHouseNature(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 10:
                                              //出租方式
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setRentType(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 11:
                                              //單位名稱
                                              illegalrentgroup.setMainrentName(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 12:
                                              //當前狀態
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setStatus(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 13:
                                              //疏散人数留在本区
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setSparseKeepnum(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 14:
                                              //疏散人数流向本市其他区
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setSparseOtherarea(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 15:
                                              //疏散人数离开本市
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setSparseLeave(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 16:
                                              //涉及就业人口数
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setWorkingPopulation(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 17:
                                              //涉及居住人口数
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setFamilyPopulation(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 18:
                                              //涉及未明确就业或居住人口
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setNotPopulation(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 19:
                                              //重点区域
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setKeyarea(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 20:
                                              //年内反复
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setRepeateyear(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 21:
                                              //提交人
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setCommitUser(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 22:
                                              //提交时间
                                              illegalrentgroup.setCommitTime(sdf.parse("2018-3-21"));
                                              break;
                                          case 23:
                                              //联系方式
                                              if(cell.getCellType() == CellType.NUMERIC){
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              illegalrentgroup.setContact(cell.getStringCellValue()==null?"":cell.getStringCellValue());
                                              break;
                                          case 24:
                                              //位置
                                              if(cell.getCellType() == CellType.NUMERIC) {
                                                  cell.setCellType(CellType.STRING);
                                              }
                                              String gridStr=mapOwnerinfoMapper.selectCoordingbyName(cell.getStringCellValue());
                                              illegalrentgroup.setGridLocation(gridStr==null?"":gridStr);
                                              break;
                                          default:
                                              json.setFlag(0);
                                              json.setMsg("err");
                                              break;
                                      }
                                  }

                              }
                              //将数据放入数据库
                              int count1=illegalrentgroupMapper.insertSelective(illegalrentgroup);
                              if(count1>0) {
                                  count += count1;
                              }
                              dest.delete();
                          }
                      }

                  }
              }
              if(count>0){
                  json.setTotleNum(count);
                  json.setMsg("ok");
                  json.setFlag(0);
                  return  json;
              }
          }catch (Exception e){
              json.setMsg("err");
              e.printStackTrace();
          }finally {
              return json;
          }
}


    /**
    * @Author 程叶同
    * @Description 删除
    * @Date 15:41 2018/1/10
    */
    public BaseWrapper deleteRent(Integer id){
        BaseWrapper baseWrapper=new BaseWrapper();
        if(id!=null&&id!=0){
            int i = illegalrentgroupMapper.deleteByPrimaryKey(id);
            if(i>0){
                baseWrapper.setMsg("ok");
                baseWrapper.setFlag(true);
                baseWrapper.setStatus(true);
            }
        }
        return  baseWrapper;
    }
    /**
    * @Author 程叶同
    * @Description 修改
    * @Date 15:46 2018/1/10
    */

    public BaseWrapper updateRent(Illegalrentgroup illegalrentgroup){
        BaseWrapper baseWrapper=new BaseWrapper();
        if(illegalrentgroup!=null){
            int i = illegalrentgroupMapper.updateByPrimaryKey(illegalrentgroup);
            if(i>0){
                baseWrapper.setStatus(true);
                baseWrapper.setFlag(true);
                baseWrapper.setMsg("修改成功");
            }
        }
        return baseWrapper;
    }

    /**
    * @Author 程叶同
    * @Description 查询byId
    * @Date 15:48 2018/1/10
    */
    public BaseWrapper getRentById(Integer id){
        BaseWrapper baseWrapper=new BaseWrapper();
        if(id!=null&&id!=0){
            Illegalrentgroup illegalrentgroup = illegalrentgroupMapper.selectByPrimaryKey(id);
            if(illegalrentgroup!=null){
                baseWrapper.setData(illegalrentgroup);
                baseWrapper.setMsg("查询成功");
                baseWrapper.setFlag(true);
                baseWrapper.setStatus(true);

            }
        }
        return baseWrapper;
    }
/**
* @Author 程叶同
* @Description  查询全部
* @Date 16:03 2018/1/10
*/

    public BaseWrapper getList(Integer page, Integer pageSize, String subStreet, String subArea, String buildingDoor, String mainrentName, boolean useFlag){
        BaseWrapper baseWrapper=new BaseWrapper();
        Map<String,Object> map=new HashMap<String,Object>();
        Map<String,Object> map1=new HashMap<String,Object>();
        PageParams pageParams=new PageParams();
        pageParams.setUseFlag(useFlag);
        pageParams.setPage(page);
        pageParams.setPageSize(pageSize);
        map.put("pageParams",pageParams);
        map.put("subStreet",subStreet);
        map.put("subArea",subArea);
        map.put("buildingDoor",buildingDoor);
        map.put("mainrentName",mainrentName);
        map1.put("subStreet",subStreet);
        map1.put("subArea",subArea);
        map1.put("buildingDoor",buildingDoor);
        map1.put("mainrentName",mainrentName);
        List<Illegalrentgroup> illegalrentgroups = illegalrentgroupMapper.selectList(map);
        int i = illegalrentgroupMapper.totalItems(map1);
        if(illegalrentgroups!=null){
            baseWrapper.setData(illegalrentgroups);
            baseWrapper.setMsg("ok");
            baseWrapper.setTotle(pageParams.getTotal());
            baseWrapper.setFlag(useFlag);
            baseWrapper.setFlag(true);
            baseWrapper.setStatus(true);
        }
        return baseWrapper;
    }



    public BaseWrapper outPutFile(HttpServletRequest request, HttpServletResponse response, String ids){
        BaseWrapper baseWrapper=new BaseWrapper();
        Map<String,Object> map=new HashMap<String,Object>();
        List<Illegalrentgroup> illegalrentgroups=illegalrentgroupMapper.selectList(map);
        if(illegalrentgroups!=null){
            for(Illegalrentgroup il:illegalrentgroups){
                if(il.getHouseNature()!=null&&il.getHouseNature()!=""){
                    switch(Integer.parseInt(il.getHouseNature())){
                        case 1: il.setHouseNature("商品房"); break;
                        case 2: il.setHouseNature("保障房"); break;
                        case 3: il.setHouseNature("经济适用房"); break;
                        case 4: il.setHouseNature("公租房"); break;
                        case 5: il.setHouseNature("廉价房"); break;
                        case 6: il.setHouseNature("其他"); break;
                    }
                }

                if(il.getRentType()!=null&&il.getRentType()!=""){
                    switch(Integer.parseInt(il.getRentType())){
                        case 1: il.setRentType("房主出租"); break;
                        case 2: il.setRentType("中介出租"); break;
                        case 3: il.setRentType("其他"); break;

                    }
                }
                if(il.getExistignProblem()!=null&&il.getExistignProblem()!=""){
                    switch(Integer.parseInt(il.getExistignProblem())){
                        case 1: il.setExistignProblem("打隔断"); break;
                        case 2: il.setExistignProblem("扰民"); break;
                        case 3: il.setExistignProblem("每间房屋出租超过两人(非亲属)"); break;
                        case 4: il.setExistignProblem("人均居住面积超过5平米"); break;
                        case 5: il.setExistignProblem("厨房阳台卫生间住人"); break;

                    }
                }
                if(il.getStatus()!=null&&il.getStatus()!=""){
                    switch(Integer.parseInt(il.getStatus())){
                        case 1: il.setStatus("核实确认"); break;
                        case 2: il.setStatus("停租"); break;
                        case 3: il.setStatus("整治中"); break;
                        case 4: il.setStatus("整治完毕"); break;

                    }
                }
            }
        }
        /*if(ids!=null&&ids!=""){
            String[] id=ids.split(",");
            Map<String,Object> map=new HashMap<String,Object>();
            map.put("id",id);
            illegalrentgroups=illegalrentgroupMapper.selectList(map);
        }*/

        try {

            HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("群租房治理工作台账表", 9);
            String[] secondTitles = {"所属街镇","所属区村","楼门房号","存在问题","房间数","隔断间数","建筑面积","居住人数","房屋来源","房屋性质","出租方式","房主姓名或中介名称","当前状态","疏散人数留在本区","疏散人数流向其他区","疏散人数流离开本市","涉及就业人口","涉及居住人口","涉及未就业或居住人口","重点区域","年内反复"};
            HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);

            // String[] beanProperty = {user.getDep().getDeptName(),"userName","userPrivName", "roleAuxiliaryName","online","sex","online","telNoDept","telNoDept","departmentPhone","email"};
            String[] beanProperty = {"subStreet","gridName","buildingDoor","existignProblem","room","partitionedRoom",
                    "constructionArea","personNumber","houseSource","houseNature","rentType","mainrentName","status","sparseKeepnum","sparseOtherarea","sparseLeave","workingPopulation","familyPopulation","notPopulation","keyarea","repeateyear"};

            HSSFWorkbook workbook = ExcelUtil.exportExcelData(workbook2, illegalrentgroups, beanProperty);
            ServletOutputStream out = response.getOutputStream();

            String filename = "群租房.xls";
            filename = FileUtils.encodeDownloadFilename(filename,
                    request.getHeader("user-agent"));
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-disposition",
                    "attachment;filename=" + filename);
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }

        return  baseWrapper;
    }
    /**
     * @Author 郭心雨
     * @Description  查询全部出租屋新
     * @Date 16:03 2018/1/10
     */

    public BaseWrapper getListNew(Integer page, Integer pageSize, String subStreet, String subArea, String buildingDoor, String mainrentName, boolean useFlag){
        BaseWrapper baseWrapper=new BaseWrapper();
        Map<String,Object> map=new HashMap<String,Object>();
        Map<String,Object> map1=new HashMap<String,Object>();
        PageParams pageParams=new PageParams();
        pageParams.setUseFlag(useFlag);
        pageParams.setPage(page);
        pageParams.setPageSize(pageSize);
        map.put("pageParams",pageParams);
        map.put("subStreet",subStreet);
        map.put("subArea",subArea);
        map.put("buildingDoor",buildingDoor);
        map.put("mainrentName",mainrentName);
        map1.put("subStreet",subStreet);
        map1.put("subArea",subArea);
        map1.put("buildingDoor",buildingDoor);
        map1.put("mainrentName",mainrentName);
        List<Illegalrentgroup> illegalrentgroups = illegalrentgroupMapper.selectListNew(map);
        int i = illegalrentgroupMapper.totalItems(map1);
        if(illegalrentgroups!=null){
            baseWrapper.setData(illegalrentgroups);
            baseWrapper.setMsg("ok");
            baseWrapper.setFlag(useFlag);
            baseWrapper.setFlag(true);
            baseWrapper.setStatus(true);
        }
        return baseWrapper;
    }

    /**
     * @Author 程叶同
     * @Description 查询byLoc
     * @Date 15:48 2018/1/10
     */
    public BaseWrapper getRentByLoc(Integer page, Integer pageSize, String gridLocation){
        BaseWrapper baseWrapper=new BaseWrapper();
        Map<String,Object> map=new HashedMap();
        PageParams pageParams=new PageParams();
        pageParams.setPage(page);
        pageParams.setPageSize(pageSize);
        map.put("pageParams",pageParams);
        map.put("gridLocation",gridLocation);
        if(gridLocation!=null){
            List<Illegalrentgroup> illegalrentgroup = illegalrentgroupMapper.selectListByLoc(map);
            for (Illegalrentgroup ill:illegalrentgroup){
                GridInfo gridName=gridInfoMapper.selectByUGridId(ill.getSubArea());
                ill.setSubArea(gridName.getGridName());
            }

            if(illegalrentgroup!=null){
                String ju=mapOwnerinfoMapper.selectByLabelName(illegalrentgroup.get(0).getGridLocation());
                illegalrentgroup.get(0).setGridLocationName(ju);
                baseWrapper.setData(illegalrentgroup);
                baseWrapper.setMsg("查询成功");
                baseWrapper.setFlag(true);
                baseWrapper.setStatus(true);

            }
        }
        return baseWrapper;
    }
}
